package by.gravity.constants;

import java.util.Calendar;

import by.gravity.utils.DateUtil;

public class DbQueryConstants implements DBConstants {

	private static final String GET_SPECIALITY_QUERY = "SELECT " + SPECIALITY_NAME_FIELD + " FROM " + SPECIALITY_TABLE_NAME;

	private static final String GET_KURS_QUERY = "SELECT kurs.Name FROM kurs " + "WHERE kurs.ID IN( "
			+ "SELECT speciality_kurs.KursID FROM speciality_kurs " + "WHERE speciality_kurs.SpecID IN( "
			+ "SELECT speciality.ID FROM speciality " + "WHERE speciality.Name = '%1$s' ))";

	private static final String GET_GROPUS_BY_KURS_QUERY = "SELECT groups.Name FROM groups " + "WHERE groups.ID IN ( "
			+ "SELECT kurs_group.GroupID FROM kurs_group " + "WHERE kurs_group.KursID IN( " + "SELECT Kurs.ID FROM kurs "
			+ "WHERE kurs.ID IN( " + "SELECT speciality_kurs.KursID FROM speciality_kurs " + "WHERE speciality_kurs.SpecID IN( "
			+ "SELECT speciality.ID FROM speciality " + "WHERE speciality.Name = '%1$s' " + ") AND kurs.Name = '%2$s')))";

	private static final String GET_PAIRS_QUERY = "SELECT pair.LessonDate,lesson_number.TimeStart,lesson_number.TimeFinish, teachers.Name AS "
			+ PAIR_TEACHER_NAME
			+ ",lessons.Name AS "
			+ PAIR_LESSON_NAME
			+ ",auditories.Name AS "
			+ PAIR_AUDITORY_NAME
			+ " from pair "
			+ "INNER JOIN (teachers,lessons,lesson_number,auditories) "
			+ "ON pair.TeacherID = teachers.ID "
			+ "AND pair.LessonID = lessons.ID "
			+ "AND pair.LessonNumberID = lesson_number.ID "
			+ "AND pair.AuditoryID = auditories.ID "
			+ "AND pair.Kurs_Group_ID IN ( "
			+ "SELECT kurs_group.ID FROM kurs_group "
			+ "WHERE kurs_group.GroupID IN( "
			+ "SELECT groups.ID FROM groups "
			+ "WHERE groups.ID IN ( "
			+ "SELECT kurs_group.GroupID FROM kurs_group "
			+ "WHERE kurs_group.KursID IN ( "
			+ "SELECT Kurs.ID FROM kurs "
			+ "WHERE kurs.ID IN( "
			+ "SELECT speciality_kurs.KursID FROM speciality_kurs "
			+ "WHERE speciality_kurs.SpecID IN( "
			+ "SELECT speciality.ID FROM speciality "
			+ "WHERE speciality.Name = '%1$s' "
			+ ") AND kurs.Name = '%2$s' ))"
			+ "AND groups.Name = '%3$s' )))" + "AND pair.LessonDate BETWEEN '%4$s' AND '%5$s' " + "ORDER by pair.LessonDate,lesson_number.TimeStart";

	public static final String getSpecialityQuery() {
		return GET_SPECIALITY_QUERY;
	}

	public static final String getKurs(String specialityName) {
		return String.format(GET_KURS_QUERY, specialityName);
	}

	public static final String getGroups(String specialityName, String kursName) {
		return String.format(GET_GROPUS_BY_KURS_QUERY, specialityName, kursName);
	}

	public static final String getPairs(String specialityName, String kursName, String groupName, long weekStartDate) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTimeInMillis(weekStartDate);

		String dayStart = DateUtil.getSqlDate(calendar);

		calendar.add(Calendar.DAY_OF_MONTH, 6);
		String dayFinish = DateUtil.getSqlDate(calendar);
		return String.format(GET_PAIRS_QUERY, specialityName, kursName, groupName, dayStart, dayFinish);
	}
}
